package com.ycw.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ycw.dao.LogisticsDao;
import com.ycw.util.DBUtil;

public class LogisticsDaoImpl implements LogisticsDao {
	private static DBUtil db = new DBUtil();
	private static LogisticsDaoImpl ldi;
	private Connection conn;
	
	private PreparedStatement ps;
	private ResultSet rs;
	
	private String sqlCount;
	private List<Object> list;
	
	@Override
	public Map<String, Object> queryAllLogisticsData(int pageNum) {
		pageNum = (pageNum-1)*10; 
		String sql = "select * from logistics limit ?,10";
		
		Map<String, Object> resMap = new HashMap<>();
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, pageNum);
			rs = ps.executeQuery();
			while(rs.next()){
				Map<String, Object> map = new HashMap<>();
				map.put("l_id", rs.getInt("l_id"));
				map.put("l_jobnumber", rs.getInt("l_jobnumber"));
				map.put("l_name", rs.getString("l_name"));
				map.put("l_age", rs.getInt("l_age"));
				map.put("l_gender", rs.getString("l_gender"));
				map.put("l_phone", rs.getString("l_phone"));
				map.put("l_number", rs.getString("l_number"));
				map.put("l_address", rs.getString("l_address"));
				Integer l_id= rs.getInt("l_id");
				resMap.put(l_id.toString(), map);
				
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			db.closeConn();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resMap;
	}

	@Override
	public int queryAllLogisticsData() {
		String sql = "select count(*) from logistics";
		int Count = 0;
		try {
			try {
				conn = db.getConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()){
				Count = rs.getInt("count(*)");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			db.closeConn();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return Count;
	}

	@Override
	public Map<String, Object> queryLogisticsDataByID(int l_id) {
		String sql = "select * from logistics where l_id=?";
		
		Map<String, Object> map = new HashMap<>();
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1,l_id);
			rs = ps.executeQuery();
			while(rs.next()){
				map.put("l_id", rs.getInt("l_id"));
				map.put("l_jobnumber", rs.getInt("l_jobnumber"));
				map.put("l_name", rs.getString("l_name"));
				map.put("l_age", rs.getInt("l_age"));
				map.put("l_gender", rs.getString("l_gender"));
				map.put("l_phone", rs.getString("l_phone"));
				map.put("l_number", rs.getString("l_number"));
				map.put("l_address", rs.getString("l_address"));		
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			db.closeConn();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return map;
	}

	@Override
	public boolean addLogisticsData(List<Object> list) {
		String sql = "insert into logistics(l_jobnumber,l_name,l_age,l_gender,l_phone,l_number,l_address) values(?,?,?,?,?,?,?)";
		
		int i1 = 0;
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i+1, list.get(i));
			}
			i1 = ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(i1>0){
			return true;
		}
		return false;
	}
	
	/**
	 * 添加时工号不能重
	 * @param l_jobnumber
	 * @return
	 */
	@Override
	public boolean queryLogisticsL_jobnumber(int l_jobnumber) {
		String sql = "select * from logistics  where l_jobnumber=?";
		boolean flage = true;
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, l_jobnumber);
			rs = ps.executeQuery();
			if(rs.next()){
				flage = false;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return flage;
	}

	@Override
	public boolean deleteLogisticsDataById(int l_id) {
		String sql = "delete from logistics where l_id=?";
		int i = 0;
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, l_id);
			i = ps.executeUpdate();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(i>0){
			return true;
		}
		return false;
	}

	@Override
	public boolean alterLogisticsDataById(List<Object> list) {
		String sql = "UPDATE logistics set l_jobnumber=?,l_name=?,l_age=?,l_gender=?,l_phone=?,l_number=?,l_address=? where l_id=?";
		int j = 0;
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i+1, list.get(i));
			}
			j = ps.executeUpdate();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				db.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(j>0){
			return true;
		}
		return false;
	}
	
	private LogisticsDaoImpl(){}
	
	public static LogisticsDaoImpl getInstance(){
		if(ldi==null){
			ldi = new LogisticsDaoImpl();
		}

		return ldi;
	}

	@Override
	public Map<String, Object> queryAllLogisticsDataByNameOrPhone(String l_name, String l_phone, int pageNum) {
		pageNum = (pageNum-1)*10;
		
		String sql = "select * from logistics where 1=1";
		String sqlCount = "select count(*) from logistics where 1=1";
		StringBuffer sb = new StringBuffer(sql);
		StringBuffer sbCount = new StringBuffer(sqlCount);
		
		List<Object> list = new ArrayList<Object>();
		List<Object> listCount = new ArrayList<Object>();
		if(l_name!=""){
			sb.append(" and l_name=?");
			sbCount.append(" and l_name=?");
			list.add(l_name);
			listCount.add(l_name);
		}
		if(l_phone!=""){
			sb.append(" and l_phone=?");
			sbCount.append(" and l_phone=?");
			list.add(l_phone);
			listCount.add(l_phone);
		}
		this.sqlCount = sbCount.toString();
		this.list = listCount;
		sb.append(" limit ?,10");
		list.add(pageNum);
		sql = sb.toString();
		
		Map<String, Object> resMap = new HashMap<>();
		try {
			conn = db.getConn();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i+1, list.get(i));
			}
			rs = ps.executeQuery();
			while(rs.next()){
				Map<String, Object> map = new HashMap<>();
				map.put("l_id", rs.getInt("l_id"));
				map.put("l_jobnumber", rs.getInt("l_jobnumber"));
				map.put("l_name", rs.getString("l_name"));
				map.put("l_age", rs.getInt("l_age"));
				map.put("l_gender", rs.getString("l_gender"));
				map.put("l_phone", rs.getString("l_phone"));
				map.put("l_number", rs.getString("l_number"));
				map.put("l_address", rs.getString("l_address"));
				Integer l_id= rs.getInt("l_id");
				resMap.put(l_id.toString(), map);
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resMap;
	}

	@Override
	public int queryAllLogisticsDataByNameOrPhone() {
		int Count = 0;
		try {
			try {
				conn = db.getConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			ps = conn.prepareStatement(sqlCount);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i+1, list.get(i));
			}
			rs = ps.executeQuery();
			if(rs.next()){
				Count = rs.getInt("count(*)");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			db.closeConn();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return Count;
	}

}
